﻿/**********************************************************************************
 * 作者：Darren Zhou
 * 小组：开发小组
 * 说明： SQLHelper数据库助手类
 *创建日期：2017-07-28 00:23:04
 * 版本号：V1.0.0
* *********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Common.DataBase.Interface;
using Common.DataBase.Model;
using System.Reflection;
using CommonUtils.Attributes;

namespace Common.DataBase.Implement {

  public abstract class SQLHelper : ISQLHelper {

    private static log4net.ILog _logger = log4net.LogManager.GetLogger(typeof(SQLHelper));

    protected const int TIMEOUT = 999;
    protected string connectString = null;
    protected string queryUserTableSQL = null;
    protected string queryColumnsByTableNameSQL = null;
    protected static Dictionary<string, string> dataTypeMap = new Dictionary<string, string>();


    protected static DbConnection connection = null;

    protected abstract void PrepareConnection();

    public SQLHelper() {
      PrepareConnection();
    }

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    public string ConnectString {
      get {
        return connectString;
      }
    }

    /// <summary>
    /// 数据库类型
    /// </summary>
    public virtual DataBaseType DBType {
      get {
        return DataBaseType.Unknown;
      }
    }

    /// <summary>
    /// 查询所有用户表的字符串
    /// </summary>
    public virtual string QueryUserTableSQL {
      get {
        return queryUserTableSQL;
      }
    }

    /// <summary>
    /// 根据指定表名查询列信息的SQL
    /// </summary>
    public virtual string QueryColumnsByTableNameSQL {
      get {
        return queryColumnsByTableNameSQL;
      }
    }
    /// <summary>
    /// 数据类型映射关系
    /// </summary>
    public virtual Dictionary<string, string> DataTypeMap {
      get {
        return dataTypeMap;
      }
    }



    /// <summary>
    /// 构造SQL参数
    /// </summary>
    /// <param name="parameterName">参数名</param>
    /// <param name="value">参数值</param>
    /// <returns>参数对象</returns>
    public abstract DbParameter DbParam(string parameterName, object value);

    #region "打开数据库连接"
    /// <summary>
    /// 打开数据库连接
    /// </summary>
    /// <returns></returns>
    public virtual DbConnection OpenConnection() {
      if (connection != null && connection.State != ConnectionState.Open) {
        connection.Open();
      }
      return connection;
    }
    #endregion


    #region "关闭数据库连接"
    /// <summary>
    /// 打开数据库连接
    /// </summary>
    /// <returns></returns>
    public virtual void CloseConnection() {
      if (connection != null && connection.State != ConnectionState.Open) {
        connection.Close();
      }
    }
    #endregion


    /// <summary>
    /// 执行不带参数的增删改SQL语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句或存储 过程</param>
    /// <returns>返回受影响的记录数</returns>
    public int ExecuteNonQuery(string commandText, bool isClose = true) {
      return ExecuteNonQuery(commandText, CommandType.Text, isClose);
    }


    /// <summary>
    /// 执行不带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    public int ExecuteNonQuery(string commandText, CommandType commandType = CommandType.Text, bool isClose = true) {
      return ExecuteNonQuery(commandText, commandType, null, isClose);
    }
    
    /// <summary>
    /// 执行带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    public abstract int ExecuteNonQuery(string commandText, CommandType commandType, DbParameter[] parameters = null, bool isClose = true);


    /// <summary>
    /// 执行不带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public DataTable ExecuteQuery(string commandText, bool isClose = true) {
      return ExecuteQuery(commandText, CommandType.Text, isClose);
    }

    /// <summary>
    /// 执行不带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public DataTable ExecuteQuery(string commandText, CommandType commandType, bool isClose = true) {
      return ExecuteQuery(commandText, commandType, null, isClose);
    }


    /// <summary>
    /// 执行带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public abstract DataTable ExecuteQuery(string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true);


    /// <summary>
    /// 查询对象集合
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public List<T> FindList<T>(string commandText, bool isClose = true) where T : new() {
      return FindList<T>(commandText, CommandType.Text, isClose);
    }


    /// <summary>
    /// 查询对象集合
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public List<T> FindList<T>(string commandText, CommandType commandType, bool isClose = true) where T : new() {
      return FindList<T>(commandText, CommandType.Text, null, isClose);
    }


    /// <summary>
    /// 查询对象集合
    /// 该方法执行带参数的SQL查询语句
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    public virtual List<T> FindList<T>(string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true) where T : new() {
      DataTable dataTable = ExecuteQuery(commandText, commandType, parameters, isClose);
      PropertyInfo[] properties = typeof(T).GetProperties();
      List<T> entitys = new List<T>();//创建实体
      if (dataTable != null && dataTable.Rows.Count > 0) {
        foreach (DataRow dataRow in dataTable.Rows) {
          T entity = new T();
          foreach (PropertyInfo property in properties) {
            DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
            if ( dbColumnAttr != null ) {
              object value = dataRow[dbColumnAttr.ColumnName];
              if ( value != DBNull.Value && property.CanWrite ) {
                property.SetValue(entity, Convert.ChangeType(value, property.PropertyType), null);
              }
            }
          }
          entitys.Add(entity);
        }
      }
      return entitys;
    }

    
    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    public object ExecuteScalar(string commandText, bool isClose = true) {
      return ExecuteScalar(commandText, null, isClose);
    }

    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    public object ExecuteScalar(string commandText, DbParameter[] parameters, bool isClose = true) {
      return ExecuteScalar(commandText, parameters, CommandType.Text, isClose);
    }

    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    public abstract object ExecuteScalar(string commandText, DbParameter[] parameters, CommandType commandType, bool isClose = true);



    /// <summary>
    /// 生成Insert语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public virtual string GenerateInsertSQL(Type type) {
      string insertSQL = string.Empty;
      try {
        _logger.Info(string.Format("生成实体类{0}的Insert SQL语句", type.FullName));
        DBTable dbTable = (DBTable)type.GetCustomAttribute(typeof(DBTable));
        if ( dbTable != null && !string.IsNullOrWhiteSpace(dbTable.TableName) ) {
          string fields = string.Empty;
          string parameters = string.Empty;
          PropertyInfo[] propertys = type.GetProperties();
          foreach ( PropertyInfo property in propertys ) {
            DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
            if ( dbColumnAttr != null ) {
              fields += string.Format("{0},", dbColumnAttr.ColumnName);
              parameters += string.Format("@{0},", property.Name);
            }
          }
          insertSQL = string.Format("INSERT INTO {0}({1})  VALUES({2});", dbTable.TableName, fields.Trim(','), parameters.Trim(','));          
        }
      } catch (Exception ex) {
        _logger.Error(ex);
      }
      _logger.Info(string.Format("实体类{0}的Insert SQL语句    {1}", type.FullName, insertSQL));
      return insertSQL;
    }


    /// <summary>
    /// 生成Update语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public virtual string GenerateUpdateSQL(Type type) {
      string updateSQL = string.Empty;
      try {
        _logger.Info(string.Format("生成实体类{0}的Update SQL语句", type.FullName));
        DBTable dbTable = (DBTable)type.GetCustomAttribute(typeof(DBTable));
        if ( dbTable != null && !string.IsNullOrWhiteSpace(dbTable.TableName) ) {
          string fields = string.Empty;
          string where = string.Empty;
          PropertyInfo[] propertys = type.GetProperties();
          foreach ( PropertyInfo property in propertys ) {
            DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
            if ( dbColumnAttr != null ) {
              if ( dbColumnAttr.Primariy == true ) {
                where += string.Format(" {0}=@{1} AND", dbColumnAttr.ColumnName, property.Name);
              } else {
                fields += string.Format("{0}=@{1},", dbColumnAttr.ColumnName, property.Name);
              }
            }
          }
          if ( string.IsNullOrWhiteSpace(where) ) {
            updateSQL = string.Format("UPDATE {0} SET {1} ;", dbTable.TableName, fields.Trim(','));
          } else {
            updateSQL = string.Format("UPDATE {0} SET {1} WHERE {2} ;", dbTable.TableName, fields.Trim(','), where.Substring(0,where.Length-4));
          }
        }
      } catch (Exception ex) {
        _logger.Error(ex);
      }
      _logger.Info(string.Format("实体类{0}的Update SQL语句    {1}", type.FullName, updateSQL));
      return updateSQL;
    }


    /// <summary>
    /// 生成Delete语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public virtual string GenerateDeleteSQL(Type type) {
      string deleteSQL = string.Empty;
      try {
        _logger.Info(string.Format("生成实体类{0}的Delete SQL语句", type.FullName));
        DBTable dbTable = (DBTable)type.GetCustomAttribute(typeof(DBTable));
        if ( dbTable != null && !string.IsNullOrWhiteSpace(dbTable.TableName) ) {
          string where = string.Empty;
          PropertyInfo[] propertys = type.GetProperties();
          foreach ( PropertyInfo property in propertys ) {
            DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
            if ( dbColumnAttr != null ) {
              if ( dbColumnAttr.Primariy == true ) {
                where += string.Format(" {0}=@{1} AND", dbColumnAttr.ColumnName, property.Name);
              } 
            }
          }
          if ( string.IsNullOrWhiteSpace(where) ) {
            deleteSQL = string.Format("DELETE FROM {0} ;", dbTable.TableName);
          } else {
            deleteSQL = string.Format("DELETE FROM {0} WHERE {1};", dbTable.TableName, where.Substring(0, where.Length - 4));
          }
        }
      } catch (Exception ex) {
        _logger.Error(ex);
      }
      _logger.Info(string.Format("实体类{0}的Delete SQL语句    {1}", type.FullName, deleteSQL));
      return deleteSQL;
    }


    /// <summary>
    /// 生成根据主键查询一条记录的语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public virtual string GenerateSelectSQLByPrimaryKey(Type type) {
      string selectOnebyPrimaryKeySQL = string.Empty;
      try {
        _logger.Info(string.Format("生成根据主键查询一条记录的SELECT SQL语句", type.FullName));
        DBTable dbTable = (DBTable)type.GetCustomAttribute(typeof(DBTable));
        if ( dbTable != null && !string.IsNullOrWhiteSpace(dbTable.TableName) ) {
          string fields = string.Empty;
          string where = string.Empty;
          PropertyInfo[] propertys = type.GetProperties();
          foreach ( PropertyInfo property in propertys ) {
            DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
            if ( dbColumnAttr != null ) {
              fields += string.Format("{0},", dbColumnAttr.ColumnName);
              if ( dbColumnAttr.Primariy == true ) {
                where += string.Format(" {0}=@{1} AND", dbColumnAttr.ColumnName, property.Name);
              }
            }
          }
          if ( string.IsNullOrWhiteSpace(where) ) {
            selectOnebyPrimaryKeySQL = string.Format("SELECT {0} FROM {1} ;", fields,dbTable.TableName);
          } else {
            selectOnebyPrimaryKeySQL = string.Format("SELECT {0} FROM {1} WHERE {2};", fields.Trim(','),dbTable.TableName, where.Substring(0, where.Length - 4));
          }
        }
      } catch (Exception ex) {
        _logger.Error(ex);
      }
      _logger.Info(string.Format("根据主键查询一条记录的SELECT SQL语句    {1}", type.FullName, selectOnebyPrimaryKeySQL));
      return selectOnebyPrimaryKeySQL;
    }

    /// <summary>
    /// 生成Insert语句对应的参数
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t">实体对象</param>
    /// <returns></returns>
    public virtual DbParameter[] ConstructEntityParameters<T>(T t) {
      List<DbParameter> parametersList = new List<DbParameter>();
      Type type = typeof(T);
      try {
        _logger.Debug(string.Format("生成类{0}的对象对应的所有属性参数", type.FullName));
        PropertyInfo[] propertys = type.GetProperties();
        foreach (PropertyInfo property in propertys) {
          DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
          if ( dbColumnAttr != null ) {
            parametersList.Add(DbParam("@" + property.Name, property.GetValue(t, null)));
          }          
        }
      } catch (Exception ex) {
        _logger.Error(ex);
        parametersList = new List<DbParameter>();
        return parametersList.ToArray();
      }
      _logger.Debug(string.Format("生成类{0}的对象对应的所有属性参数成功。", type.FullName));
      return parametersList.ToArray();
    }

    /// <summary>
    /// 生成主键参数
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t">实体对象</param>
    /// <returns></returns>
    public DbParameter[] ConstructPrimaryKeyParameter<T>(T t) {
      List<DbParameter> parametersList = new List<DbParameter>();
      Type type = typeof(T);
      try {
        _logger.Debug(string.Format("生成类{0}的对象对应的主键参数成功", type.FullName));
        PropertyInfo[] propertys = type.GetProperties();
        foreach (PropertyInfo property in propertys) {
          DBColumn dbColumnAttr = (DBColumn)Attribute.GetCustomAttribute(property, typeof(DBColumn));
          if ( dbColumnAttr != null  && dbColumnAttr .Primariy == true) {
            parametersList.Add(DbParam("@" + property.Name, property.GetValue(t, null)));
          }
        }
      } catch (Exception ex) {
        _logger.Error(ex);
        parametersList = new List<DbParameter>();
        return parametersList.ToArray();
      }
      _logger.Debug(string.Format("生成类{0}的对象对应的主键参数成功。", type.FullName));
      return parametersList.ToArray();
    }


    public List<Model.TableInfo> GetAllUserTables() {
      List<TableInfo> tableList = new List<TableInfo>();
      DataTable dataTable = ExecuteQuery(QueryUserTableSQL, false);
      foreach (DataRow dataRow in dataTable.Rows) {
        TableInfo table = new TableInfo();
        DataColumn column = dataTable.Columns[0];
        object tableName = dataRow[column.ColumnName];
        if (tableName != DBNull.Value) {
          table.TableName = tableName.ToString();
          DataTable dataColumnsTable = ExecuteQuery(string.Format(QueryColumnsByTableNameSQL, table.TableName), false);
          foreach (DataRow fieldRow in dataColumnsTable.Rows) {
            ColumnInfo field = new ColumnInfo();
            object columnName = fieldRow[1];
            object columnType = fieldRow[2];
            object columnDefault = fieldRow[4];
            object columnPrimary = fieldRow[5];
            if (columnName != DBNull.Value) {
              field.Name = columnName.ToString();
              field.Type = columnType == DBNull.Value ? "" : columnType.ToString();
              field.DefaultValue = columnDefault == DBNull.Value ? "" : columnDefault.ToString();
              field.IsPrimary = columnPrimary.ToString() == "1" ? true : false;
              table.Columns[field.Name] = field;
            }
          }
        }
        tableList.Add(table);
      }
      return tableList;
    }



    public void Dispose() {
      if (connection != null && connection.State == ConnectionState.Open) {
        connection.Close();
      }
    }



    #region 批量导入DataTable

    //public void BulkToDB(string tableName, DataTable dt)
    //{
    //    SQLiteConnection sqlConn = new SQLiteConnection(
    //        ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
    //    sqlite
    //    SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
    //    bulkCopy.DestinationTableName = tableName;
    //    bulkCopy.BatchSize = dt.Rows.Count;

    //    try
    //    {
    //        sqlConn.Open();
    //        if (dt != null && dt.Rows.Count != 0)
    //            bulkCopy.WriteToServer(dt);
    //    }
    //    catch (Exception ex)
    //    {
    //        throw ex;
    //    }
    //    finally
    //    {
    //        sqlConn.Close();
    //        if (bulkCopy != null)
    //            bulkCopy.Close();
    //    }
    //}

    #endregion 批量导入DataTable
  }
}
